using System;
using System.Data;
using System.Data.SqlClient;

namespace MessageBoard
{
	/// <summary>
	/// Summary description for UsersInGroups.
	/// </summary>
	public class UsersInGroups : Connection
	{
		private User _User;
		private UserGroup _UserGroup;

		public UsersInGroups()
		{
		}

		public static UsersInGroups Get(System.Data.DataRow dr)
		{
			UsersInGroups relation = new UsersInGroups();
			relation.User = User.Get(int.Parse(dr["UserId"].ToString()));
			relation.UserGroup = UserGroup.Get(int.Parse(dr["UserGroupId"].ToString()));
			return relation;
		}

		public static System.Collections.ArrayList GetAll(User user)
		{
			System.Collections.ArrayList arrayList = new System.Collections.ArrayList();
			DataSet ds = new DataSet();
			SqlDataAdapter getData = new SqlDataAdapter("SELECT * FROM [" + Connection.tblUsersInGroups + "] WHERE [UserId] = @UserId", DbConnection);
			getData.SelectCommand.Parameters.Add("@UserId", user.Id);
			getData.Fill(ds);
			foreach(System.Data.DataRow dr in ds.Tables[0].Rows)
			{
				arrayList.Add(Get(dr));
			}
			return arrayList;
		}

		public static System.Collections.ArrayList GetAll(UserGroup userGroup)
		{
			System.Collections.ArrayList arrayList = new System.Collections.ArrayList();
			DataSet ds = new DataSet();
			SqlDataAdapter getData = new SqlDataAdapter("SELECT * FROM [" + Connection.tblUsersInGroups + "] WHERE [UserGroupId] = @UserGroupId", DbConnection);
			getData.SelectCommand.Parameters.Add("@UserGroupId", userGroup.Id);
			getData.Fill(ds);
			foreach(System.Data.DataRow dr in ds.Tables[0].Rows)
			{
				arrayList.Add(Get(dr));
			}
			return arrayList;
		}

		public void Insert()
		{
			DataSet ds = new DataSet();
			SqlDataAdapter getData = new SqlDataAdapter(@"
			INSERT INTO " + Connection.tblUsersInGroups + @" 
			([UserId], [UserGroupId])
			VALUES
			(@UserId, @UserGroupId);
			", DbConnection);
			getData.SelectCommand.Parameters.Add("@UserId", _User.Id);
			getData.SelectCommand.Parameters.Add("@UserGroupId", _UserGroup.Id);
			getData.Fill(ds);
		}

		public void Delete()
		{
			DataSet ds = new DataSet();
			SqlDataAdapter getData = new SqlDataAdapter(@"
			DELETE FROM [" + Connection.tblUsersInGroups + @"] WHERE [UserId] = @UserId AND [UserGroupId] = @UserGroupId", DbConnection);
			getData.SelectCommand.Parameters.Add("@UserId", _User.Id);
			getData.SelectCommand.Parameters.Add("@UserGroupId", _UserGroup.Id);
			getData.Fill(ds);
		}

		#region Properties
		/// <summary>
		/// The user
		/// </summary>
		public User User
		{
			get
			{
				return _User;
			}
			set
			{
				_User = value;
			}
		}
		
		/// <summary>
		/// The user group
		/// </summary>
		public UserGroup UserGroup
		{
			get
			{
				return _UserGroup;
			}
			set
			{
				_UserGroup = value;
			}
		}

		#endregion
	}
}
